import java.sql.*;
public class MysqlCreateTable {
	public static void main(String[] args) {
		  System.out.println("MySQL Connect Example.");
		  Connection conn = null;
		  String url = "jdbc:mysql://localhost:3306/";
		  String dbName = "pichubdb";
		  String driver = "com.mysql.jdbc.Driver";
		  String userName = "pichub"; 
		  String password = "Mark1990";
		  try {
		
		  Class.forName(driver).newInstance();
		  conn = DriverManager.getConnection(url + dbName ,userName,password);
		  System.out.println("Connected to the database");
		  Statement myState = conn.createStatement();
		  
		  
		  /* 
		   * Table image
		   * id(int), name(String), type(String), address(String), city(String), dt(DateTime), size(int), hotness(int), imageData(blob) 
		   */
		  String createTable = "CREATE TABLE image(id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, " +
		  		"name VARCHAR(50) NOT NULL DEFAULT '', " + 
				"type VARCHAR(25) NOT NULL DEFAULT '', " +
				"adress VARCHAR(50) NOT NULL DEFAULT '', " +
				"city VARCHAR(25) NOT NULL DEFAULT '', " +
				"dt TIMESTAMP NOT NULL, " +
		  		"size INTEGER NOT NULL DEFAULT 0, " + 
		  		"hotness INTEGER NOT NULL DEFAULT 0, " + 
		  		"imageData BLOB NOT NULL, " +
		  		"primary key(id));";
		  
		  /*
		   * Table comments
		   * id(int), username(String), content(String), dt(DateTime), imageId(int)
		   */
		  
		  myState.executeUpdate(createTable);
		  createTable = "CREATE TABLE comments(id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, " +
			  		"username VARCHAR(25) NOT NULL DEFAULT '', " + 
					"content VARCHAR(200) NOT NULL DEFAULT '', " +
					"dt TIMESTAMP NOT NULL, " +
			  		"imageId INTEGER UNSIGNED NOT NULL, "+
			  		"primary key(id));";
			  
			  myState.executeUpdate(createTable);
		  
		  Statement st = conn.createStatement();
		  conn.close();
		  System.out.println("Disconnected from database");
		  } catch (Exception e) {
			  System.out.println("Failed to connect to database");
			  e.printStackTrace();
		  }
	}
}
